CREATE OR REPLACE PACKAGE GETCLOSEDORDERDATA AS 
   PROCEDURE POPULATE13MONTHORDERS; 
   PROCEDURE POPULATE13MONTHBFRORDERS;
   PROCEDURE POPULATE13MONTHORDERLINES;
   PROCEDURE POPULATEBFR13MONTHORDERLINES;
END GETCLOSEDORDERDATA; 
/

CREATE OR REPLACE PACKAGE BODY GETCLOSEDORDERDATA AS 
 
   PROCEDURE POPULATE13MONTHORDERS AS 
   BEGIN 
     INSERT INTO TBL_CLOSED_ORDERS_IN_13MONTHS 
        SELECT 
        CLOSED_SALES_SEQ.NEXTVAL,
        HEADER_ID,
        ORDER_NUMBER
        FROM
        (
                SELECT 
                        DISTINCT
                        LINES.HEADER_ID HEADER_ID,   
                        HEADER.ORDER_NUMBER ORDER_NUMBER
                        FROM
                        OE_ORDER_LINES_ALL LINES,
                        OE_ORDER_HEADERS_ALL HEADER
                        WHERE 
                        LINES.SCHEDULE_SHIP_DATE >=ADD_MONTHS(SYSDATE,-13)
                        AND LINES.FLOW_STATUS_CODE != 'CANCELLED'
                        AND LINES.HEADER_ID = HEADER.HEADER_ID
                        AND HEADER.ORDER_TYPE_ID != 1009
                MINUS
                        SELECT 
                        DISTINCT
                        LINES.HEADER_ID,
                        HEADER.ORDER_NUMBER
                        FROM
                        OE_ORDER_LINES_ALL LINES,
                        OE_ORDER_HEADERS_ALL HEADER
                        WHERE
                        --LINES.SCHEDULE_SHIP_DATE >=ADD_MONTHS(SYSDATE,-13)
                        LINES.FLOW_STATUS_CODE != 'CLOSED'
                        AND LINES.FLOW_STATUS_CODE != 'CANCELLED'
                        AND LINES.HEADER_ID = HEADER.HEADER_ID
                        AND HEADER.ORDER_TYPE_ID != 1009
        );
     COMMIT;

    
   END POPULATE13MONTHORDERS; 

   PROCEDURE POPULATE13MONTHBFRORDERS AS 
   BEGIN 
     INSERT INTO TBL_CLOSED_ORDERS_BFR_13MONTHS
        SELECT 
        CLOSED_SALES_SEQ.NEXTVAL,
        HEADER_ID,
        ORDER_NUMBER
        FROM
        (
                (
                SELECT 
                        DISTINCT
                        LINES.HEADER_ID HEADER_ID,
                        HEADER.ORDER_NUMBER ORDER_NUMBER
                        FROM
                        OE_ORDER_LINES_ALL LINES,
                        OE_ORDER_HEADERS_ALL HEADER,
                        CSI.CSI_ITEM_INSTANCES CSI, 
                        OKC_K_ITEMS OKI, 
                        OKC_K_LINES_B OKLB,
                        OKC_K_HEADERS_B OKHB, 
                        OKC_LINE_STYLES_V OLSV1, 
                        OKC_LINE_STYLES_V OLSV2
                        WHERE 
                        LINES.SCHEDULE_SHIP_DATE < ADD_MONTHS(SYSDATE,-13)
                        AND LINES.FLOW_STATUS_CODE = 'CLOSED'
                        AND LINES.HEADER_ID = HEADER.HEADER_ID
                        AND HEADER.ORDER_TYPE_ID != 1009
                        AND LINES.LINE_ID = CSI.LAST_OE_ORDER_LINE_ID
                        AND CSI.INSTANCE_ID = OKI.OBJECT1_ID1
                        AND OKI.JTOT_OBJECT1_CODE ='OKX_CUSTPROD'
                        AND OKI.DNZ_CHR_ID = OKHB.ID
                        AND OKHB.SCS_CODE = 'WARRANTY'
                        AND OKHB.END_DATE >=ADD_MONTHS(SYSDATE,-6)
                        AND OKI.CLE_ID = OKLB.ID
                        AND OKLB.STS_CODE IN ('ACTIVE','SIGNED','EXPIRED')
                        AND OKLB.LSE_ID = OLSV1.ID        
                        AND OLSV1.LTY_CODE = 'COVER_PROD'
                        AND OLSV1.LSE_PARENT_ID = OLSV2.ID        
                        AND OLSV2.LTY_CODE IN ('EXT_WARRANTY', 'WARRANTY')
                MINUS
                        SELECT 
                        DISTINCT
                        LINES.HEADER_ID,
                        HEADER.ORDER_NUMBER
                        FROM
                        OE_ORDER_LINES_ALL LINES,
                        OE_ORDER_HEADERS_ALL HEADER
                        WHERE
                --        LINES.SCHEDULE_SHIP_DATE >=ADD_MONTHS(SYSDATE,-13)
                        LINES.FLOW_STATUS_CODE != 'CLOSED'
                        AND LINES.FLOW_STATUS_CODE != 'CANCELLED'
                        AND LINES.HEADER_ID = HEADER.HEADER_ID
                        AND HEADER.ORDER_TYPE_ID != 1009
                )
                MINUS
                 SELECT 
                        HEADER_ID,
                        ORDER_NUMBER
                        FROM
                        TBL_CLOSED_ORDERS_IN_13MONTHS
       );

      COMMIT;      

   END POPULATE13MONTHBFRORDERS; 

   PROCEDURE POPULATE13MONTHORDERLINES AS 
   BEGIN 
     INSERT INTO TBL_STAGING_CLOSED_ORDER_LINES
      SELECT 
        ORDERS.SERIAL_NUMBER,
        ORDERS.HEADER_ID, 
        ORDERS.ORDER_NUMBER, 
        HEADER.ORDERED_DATE, 
        LINES.LINE_CATEGORY_CODE,
        LINES.LINE_TYPE_ID, 
        LINES.LINE_ID, 
        LINES.LINE_NUMBER, 
        HZP.PARTY_ID, 
        HZP.EMAIL_ADDRESS,         
        HEADER.SALESREP_ID, 
        USER1.USER_NAME,
        HEADER.ATTRIBUTE1, 
        TO_NUMBER(SUBSTR(HR1.NAME,1,4)),
        HEADER.ATTRIBUTE8, 
        HEADER.ATTRIBUTE7,         
        HEADER.INVOICE_TO_ORG_ID, 
        HEADER.SHIP_TO_ORG_ID, 
        HEADER.PACKING_INSTRUCTIONS, 
        HEADER.SHIPPING_INSTRUCTIONS,
        HZLH1.LOCATION_ID,
        HZLH2.LOCATION_ID,                
        HZL.LOCATION_ID,
        HZL1.LOCATION_ID, 
        LINES.SHIPPING_METHOD_CODE, 
        LINES.SCHEDULE_SHIP_DATE, 
        LINES.PROMISE_DATE, 
        LINES.SHIP_TO_ORG_ID, 
        LINES.SHIP_FROM_ORG_ID, 
        LINES.ATTRIBUTE3, 
        LINES.PACKING_INSTRUCTIONS, 
        LINES.SHIPPING_INSTRUCTIONS,         
        LINES.UNIT_SELLING_PRICE, 
        LINES.UNIT_LIST_PRICE,         
        LINES.PRICING_QUANTITY, 
        LINES.ORDERED_QUANTITY, 
        LINES.SUBINVENTORY, 
        LINES.TAX_VALUE, 
        LINES.ATTRIBUTE1, 
        LINES.ATTRIBUTE2, 
        LINES.RETURN_CONTEXT ,
        LINES.RETURN_REASON_CODE,
        LINES.REFERENCE_LINE_ID,                                                           
                                                                                   
                                    
        LINES.REFERENCE_HEADER_ID,
        LINES.RETURN_ATTRIBUTE1,
        LINES.RETURN_ATTRIBUTE2,
        LINES.RETURN_ATTRIBUTE3,
        LINES.RETURN_ATTRIBUTE4,
        LINES.RETURN_ATTRIBUTE5,
        LINES.SERVICE_REFERENCE_LINE_ID,
        LINES.GLOBAL_ATTRIBUTE7, 
        ITEMS.INVENTORY_ITEM_ID, 
        LINES.ORDERED_ITEM, 
        ITEMS.ITEM_TYPE, 
        ITEMS.DESCRIPTION,  
        ITEMS.ITEM_CATALOG_GROUP_ID, 
        MC.SEGMENT4,
        ITEMS.PRIMARY_UNIT_OF_MEASURE, 
        HR.NAME, 
        HR.ATTRIBUTE3, 
        HR.LOCATION_ID, 
        CASH.CASH_RECEIPT_ID, 
        CASH.RECEIPT_DATE,
        SUBSTR(CASH.ATTRIBUTE8,1, INSTR(CASH.ATTRIBUTE8,'|',1,1)-1),  
        SUBSTR(CASH.ATTRIBUTE8,(INSTR(CASH.ATTRIBUTE8,'|',1,1)+1),
(INSTR(CASH.ATTRIBUTE8,'|',1,2))-INSTR(CASH.ATTRIBUTE8,'|',1,1)-1), 
        SUBSTR(CASH.ATTRIBUTE8,INSTR(CASH.ATTRIBUTE8,'|',1,2)+1, LENGTH(CASH.ATTRIBUTE8)), 
        CASH.ATTRIBUTE3,
        HZL.PROVINCE, 
        ADJUST_GST.ADJUSTED_AMOUNT, 
        ADJUST_GST.OPERAND, 
        ADJUST_PST.ADJUSTED_AMOUNT, 
        ADJUST_PST.OPERAND, 
        ADJUST_HST.ADJUSTED_AMOUNT,
        ADJUST_HST.OPERAND,         
        DELI_ASSIGN.DELIVERY_ID,
        LINES.FLOW_STATUS_CODE
        FROM
        OE_ORDER_HEADERS_ALL HEADER, 
        OE_ORDER_LINES_ALL LINES, 
        MTL_SYSTEM_ITEMS_B ITEMS, 
        MTL_ITEM_CATEGORIES MTC,
        MTL_CATEGORIES_B MC,
        AR_CASH_RECEIPTS_ALL CASH, 
        OE_PRICE_ADJUSTMENTS ADJUST_PST, 
        OE_PRICE_ADJUSTMENTS ADJUST_GST, 
        OE_PRICE_ADJUSTMENTS ADJUST_HST, 
        HR_ORGANIZATION_UNITS HR, 
        HR_ORGANIZATION_UNITS HR1,
        WSH_DELIVERY_DETAILS DELI_DET, 
        WSH_DELIVERY_ASSIGNMENTS DELI_ASSIGN, 
        HZ_CUST_ACCOUNTS HZA, 
        HZ_PARTIES HZP, 
        HZ_CUST_SITE_USES_ALL HZCSUA,
        HZ_PARTY_SITES HZPS, 
        HZ_LOCATIONS HZL,
        HZ_CUST_ACCT_SITES_ALL HZCASA,
        HZ_CUST_SITE_USES_ALL HZCSUA1, 
        HZ_PARTY_SITES HZPS1, 
        HZ_LOCATIONS HZL1,
        HZ_CUST_ACCT_SITES_ALL HZCASA1,
        HZ_CUST_SITE_USES_ALL HZCSUAH1, 
        HZ_PARTY_SITES HZPSH1, 
        HZ_LOCATIONS HZLH1,
        HZ_CUST_ACCT_SITES_ALL HZCASAH1,
        HZ_CUST_SITE_USES_ALL HZCSUAH2, 
        HZ_PARTY_SITES HZPSH2, 
        HZ_LOCATIONS HZLH2,
        HZ_CUST_ACCT_SITES_ALL HZCASAH2, 
        JTF_RS_SALESREPS SALEREP,
        FND_USER USER1,
        TBL_CLOSED_ORDERS_IN_13MONTHS ORDERS
        WHERE
        ORDERS.HEADER_ID =HEADER.HEADER_ID
        AND ORDERS.HEADER_ID = LINES.HEADER_ID
        AND HEADER.SALESREP_ID = SALEREP.SALESREP_ID(+)
        AND SALEREP.ORG_ID=22
        AND SALEREP.PERSON_ID = USER1.EMPLOYEE_ID(+)
        AND HEADER.ATTRIBUTE1 = HR1.ORGANIZATION_ID(+)
        AND LINES.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+)
        AND ITEMS.ORGANIZATION_ID (+)= 22
        AND LINES.INVENTORY_ITEM_ID = MTC.INVENTORY_ITEM_ID(+)
        AND MTC.ORGANIZATION_ID(+) = 22
        AND MTC.CATEGORY_ID = MC.CATEGORY_ID(+)
        AND MTC.CATEGORY_SET_ID = 1
        AND LINES.ATTRIBUTE7 = CASH.CASH_RECEIPT_ID(+)
        AND LINES.LINE_ID = DELI_DET.SOURCE_LINE_ID(+)
        AND LINES.SHIP_FROM_ORG_ID = DELI_DET.ORGANIZATION_ID(+)
        AND DELI_DET.DELIVERY_DETAIL_ID = DELI_ASSIGN.DELIVERY_DETAIL_ID(+)
        AND LINES.SHIP_FROM_ORG_ID = HR.ORGANIZATION_ID(+)
        AND LINES.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID(+)
        AND HZA.PARTY_ID = HZP.PARTY_ID
        AND LINES.SHIP_TO_ORG_ID = HZCSUA.SITE_USE_ID(+)
        AND HZCSUA.CUST_ACCT_SITE_ID = HZCASA.CUST_ACCT_SITE_ID(+)
        AND HZCASA.PARTY_SITE_ID = HZPS.PARTY_SITE_ID(+)
        AND HZPS.LOCATION_ID = HZL.LOCATION_ID(+)
        AND HZCSUA.SITE_USE_CODE(+) = 'SHIP_TO'
        AND HEADER.SHIP_TO_ORG_ID = HZCSUAH1.SITE_USE_ID(+)
        AND HZCSUAH1.CUST_ACCT_SITE_ID = HZCASAH1.CUST_ACCT_SITE_ID(+)
        AND HZCASAH1.PARTY_SITE_ID = HZPSH1.PARTY_SITE_ID(+)
        AND HZPSH1.LOCATION_ID = HZLH1.LOCATION_ID(+)
        AND HZCSUAH1.SITE_USE_CODE(+) = 'SHIP_TO'
        AND LINES.INVOICE_TO_ORG_ID = HZCSUA1.SITE_USE_ID(+)
        AND HZCSUA1.CUST_ACCT_SITE_ID = HZCASA1.CUST_ACCT_SITE_ID(+)
        AND HZCASA1.PARTY_SITE_ID = HZPS1.PARTY_SITE_ID(+)
        AND HZPS1.LOCATION_ID = HZL1.LOCATION_ID(+)
        AND HZCSUA1.SITE_USE_CODE(+) = 'BILL_TO'
        AND HEADER.SHIP_TO_ORG_ID = HZCSUAH2.SITE_USE_ID(+)
        AND HZCSUAH2.CUST_ACCT_SITE_ID = HZCASAH2.CUST_ACCT_SITE_ID(+)
        AND HZCASAH2.PARTY_SITE_ID = HZPSH2.PARTY_SITE_ID(+)
        AND HZPSH2.LOCATION_ID = HZLH2.LOCATION_ID(+)
        AND HZCSUAH2.SITE_USE_CODE(+) = 'BILL_TO' 
        AND LINES.HEADER_ID = ADJUST_HST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_HST.LINE_ID(+)
        AND INSTR(ADJUST_HST.TAX_CODE (+),'HST')>0
        AND ADJUST_HST.LIST_LINE_TYPE_CODE(+) ='TAX'
        AND LINES.HEADER_ID = ADJUST_PST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_PST.LINE_ID(+)
        AND INSTR(ADJUST_PST.TAX_CODE(+),'PST')>0
        AND ADJUST_PST.LIST_LINE_TYPE_CODE(+) ='TAX'
        AND LINES.HEADER_ID = ADJUST_GST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_GST.LINE_ID(+)
        AND INSTR(ADJUST_GST.TAX_CODE (+),'GST')>0
        AND ADJUST_GST.LIST_LINE_TYPE_CODE(+) ='TAX';
 
      COMMIT;      

   END POPULATE13MONTHORDERLINES; 

   PROCEDURE POPULATEBFR13MONTHORDERLINES AS 
   BEGIN 
     INSERT INTO TBL_STAGING_CLOSED_ORDER_LINES
      SELECT 
        ORDERS.SERIAL_NUMBER,
        ORDERS.HEADER_ID, 
        ORDERS.ORDER_NUMBER, 
        HEADER.ORDERED_DATE, 
        LINES.LINE_CATEGORY_CODE,
        LINES.LINE_TYPE_ID, 
        LINES.LINE_ID, 
        LINES.LINE_NUMBER, 
        HZP.PARTY_ID, 
        HZP.EMAIL_ADDRESS,         
        HEADER.SALESREP_ID, 
        USER1.USER_NAME,
        HEADER.ATTRIBUTE1, 
        TO_NUMBER(SUBSTR(HR1.NAME,1,4)),
        HEADER.ATTRIBUTE8, 
        HEADER.ATTRIBUTE7,         
        HEADER.INVOICE_TO_ORG_ID, 
        HEADER.SHIP_TO_ORG_ID, 
        HEADER.PACKING_INSTRUCTIONS, 
        HEADER.SHIPPING_INSTRUCTIONS,
        HZLH1.LOCATION_ID,
        HZLH2.LOCATION_ID,                
        HZL.LOCATION_ID,
        HZL1.LOCATION_ID,  
        LINES.SHIPPING_METHOD_CODE, 
        LINES.SCHEDULE_SHIP_DATE, 
        LINES.PROMISE_DATE, 
        LINES.SHIP_TO_ORG_ID, 
        LINES.SHIP_FROM_ORG_ID, 
        LINES.ATTRIBUTE3, 
        LINES.PACKING_INSTRUCTIONS, 
        LINES.SHIPPING_INSTRUCTIONS,         
        LINES.UNIT_SELLING_PRICE, 
        LINES.UNIT_LIST_PRICE,         
        LINES.PRICING_QUANTITY, 
        LINES.ORDERED_QUANTITY, 
        LINES.SUBINVENTORY, 
        LINES.TAX_VALUE, 
        LINES.ATTRIBUTE1, 
        LINES.ATTRIBUTE2, 
        LINES.RETURN_CONTEXT ,
        LINES.RETURN_REASON_CODE,
        LINES.REFERENCE_LINE_ID,                                                           
                                                                                   
                                    
        LINES.REFERENCE_HEADER_ID,
        LINES.RETURN_ATTRIBUTE1,
        LINES.RETURN_ATTRIBUTE2,
        LINES.RETURN_ATTRIBUTE3,
        LINES.RETURN_ATTRIBUTE4,
        LINES.RETURN_ATTRIBUTE5,
        LINES.SERVICE_REFERENCE_LINE_ID,
        LINES.GLOBAL_ATTRIBUTE7, 
        ITEMS.INVENTORY_ITEM_ID, 
        LINES.ORDERED_ITEM, 
        ITEMS.ITEM_TYPE, 
        ITEMS.DESCRIPTION,  
        ITEMS.ITEM_CATALOG_GROUP_ID, 
        MC.SEGMENT4,
        ITEMS.PRIMARY_UNIT_OF_MEASURE, 
        HR.NAME, 
        HR.ATTRIBUTE3, 
        HR.LOCATION_ID, 
        CASH.CASH_RECEIPT_ID, 
        CASH.RECEIPT_DATE,
        SUBSTR(CASH.ATTRIBUTE8,1, INSTR(CASH.ATTRIBUTE8,'|',1,1)-1),  
        SUBSTR(CASH.ATTRIBUTE8,(INSTR(CASH.ATTRIBUTE8,'|',1,1)+1),
(INSTR(CASH.ATTRIBUTE8,'|',1,2))-INSTR(CASH.ATTRIBUTE8,'|',1,1)-1), 
        SUBSTR(CASH.ATTRIBUTE8,INSTR(CASH.ATTRIBUTE8,'|',1,2)+1, LENGTH(CASH.ATTRIBUTE8)), 
        CASH.ATTRIBUTE3,
        HZL.PROVINCE, 
        ADJUST_GST.ADJUSTED_AMOUNT, 
        ADJUST_GST.OPERAND, 
        ADJUST_PST.ADJUSTED_AMOUNT, 
        ADJUST_PST.OPERAND, 
        ADJUST_HST.ADJUSTED_AMOUNT,
        ADJUST_HST.OPERAND,         
        DELI_ASSIGN.DELIVERY_ID,
        LINES.FLOW_STATUS_CODE
        FROM
        OE_ORDER_HEADERS_ALL HEADER, 
        OE_ORDER_LINES_ALL LINES, 
        MTL_SYSTEM_ITEMS_B ITEMS, 
        MTL_ITEM_CATEGORIES MTC,
        MTL_CATEGORIES_B MC,
        AR_CASH_RECEIPTS_ALL CASH, 
        OE_PRICE_ADJUSTMENTS ADJUST_PST, 
        OE_PRICE_ADJUSTMENTS ADJUST_GST, 
        OE_PRICE_ADJUSTMENTS ADJUST_HST, 
        HR_ORGANIZATION_UNITS HR, 
        HR_ORGANIZATION_UNITS HR1,
        WSH_DELIVERY_DETAILS DELI_DET, 
        WSH_DELIVERY_ASSIGNMENTS DELI_ASSIGN, 
        HZ_CUST_ACCOUNTS HZA, 
        HZ_PARTIES HZP, 
        HZ_CUST_SITE_USES_ALL HZCSUA,
        HZ_PARTY_SITES HZPS, 
        HZ_LOCATIONS HZL,
        HZ_CUST_ACCT_SITES_ALL HZCASA,
        HZ_CUST_SITE_USES_ALL HZCSUA1, 
        HZ_PARTY_SITES HZPS1, 
        HZ_LOCATIONS HZL1,
        HZ_CUST_ACCT_SITES_ALL HZCASA1,
        HZ_CUST_SITE_USES_ALL HZCSUAH1, 
        HZ_PARTY_SITES HZPSH1, 
        HZ_LOCATIONS HZLH1,
        HZ_CUST_ACCT_SITES_ALL HZCASAH1,
        HZ_CUST_SITE_USES_ALL HZCSUAH2, 
        HZ_PARTY_SITES HZPSH2, 
        HZ_LOCATIONS HZLH2,
        HZ_CUST_ACCT_SITES_ALL HZCASAH2, 
        JTF_RS_SALESREPS SALEREP,
        FND_USER USER1,
        TBL_CLOSED_ORDERS_BFR_13MONTHS ORDERS
        WHERE
        ORDERS.HEADER_ID =HEADER.HEADER_ID
        AND ORDERS.HEADER_ID = LINES.HEADER_ID
        AND HEADER.SALESREP_ID = SALEREP.SALESREP_ID(+)
        AND SALEREP.ORG_ID=22
        AND SALEREP.PERSON_ID = USER1.EMPLOYEE_ID(+)
        AND HEADER.ATTRIBUTE1 = HR1.ORGANIZATION_ID(+)
        AND LINES.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID(+)
        AND ITEMS.ORGANIZATION_ID (+)= 22
        AND LINES.INVENTORY_ITEM_ID = MTC.INVENTORY_ITEM_ID(+)
        AND MTC.ORGANIZATION_ID(+) = 22
        AND MTC.CATEGORY_ID = MC.CATEGORY_ID(+)
        AND MTC.CATEGORY_SET_ID = 1
        AND LINES.ATTRIBUTE7 = CASH.CASH_RECEIPT_ID(+)
        AND LINES.LINE_ID = DELI_DET.SOURCE_LINE_ID(+)
        AND LINES.SHIP_FROM_ORG_ID = DELI_DET.ORGANIZATION_ID(+)
        AND DELI_DET.DELIVERY_DETAIL_ID = DELI_ASSIGN.DELIVERY_DETAIL_ID(+)
        AND LINES.SHIP_FROM_ORG_ID = HR.ORGANIZATION_ID(+)
        AND LINES.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID(+)
        AND HZA.PARTY_ID = HZP.PARTY_ID
        AND LINES.SHIP_TO_ORG_ID = HZCSUA.SITE_USE_ID(+)
        AND HZCSUA.CUST_ACCT_SITE_ID = HZCASA.CUST_ACCT_SITE_ID(+)
        AND HZCASA.PARTY_SITE_ID = HZPS.PARTY_SITE_ID(+)
        AND HZPS.LOCATION_ID = HZL.LOCATION_ID(+)
        AND HZCSUA.SITE_USE_CODE(+) = 'SHIP_TO'
        AND HEADER.SHIP_TO_ORG_ID = HZCSUAH1.SITE_USE_ID(+)
        AND HZCSUAH1.CUST_ACCT_SITE_ID = HZCASAH1.CUST_ACCT_SITE_ID(+)
        AND HZCASAH1.PARTY_SITE_ID = HZPSH1.PARTY_SITE_ID(+)
        AND HZPSH1.LOCATION_ID = HZLH1.LOCATION_ID(+)
        AND HZCSUAH1.SITE_USE_CODE(+) = 'SHIP_TO'
        AND LINES.INVOICE_TO_ORG_ID = HZCSUA1.SITE_USE_ID(+)
        AND HZCSUA1.CUST_ACCT_SITE_ID = HZCASA1.CUST_ACCT_SITE_ID(+)
        AND HZCASA1.PARTY_SITE_ID = HZPS1.PARTY_SITE_ID(+)
        AND HZPS1.LOCATION_ID = HZL1.LOCATION_ID(+)
        AND HZCSUA1.SITE_USE_CODE(+) = 'BILL_TO'
        AND HEADER.SHIP_TO_ORG_ID = HZCSUAH2.SITE_USE_ID(+)
        AND HZCSUAH2.CUST_ACCT_SITE_ID = HZCASAH2.CUST_ACCT_SITE_ID(+)
        AND HZCASAH2.PARTY_SITE_ID = HZPSH2.PARTY_SITE_ID(+)
        AND HZPSH2.LOCATION_ID = HZLH2.LOCATION_ID(+)
        AND HZCSUAH2.SITE_USE_CODE(+) = 'BILL_TO' 
        AND LINES.HEADER_ID = ADJUST_HST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_HST.LINE_ID(+)
        AND INSTR(ADJUST_HST.TAX_CODE (+),'HST')>0
        AND ADJUST_HST.LIST_LINE_TYPE_CODE(+) ='TAX'
        AND LINES.HEADER_ID = ADJUST_PST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_PST.LINE_ID(+)
        AND INSTR(ADJUST_PST.TAX_CODE(+),'PST')>0
        AND ADJUST_PST.LIST_LINE_TYPE_CODE(+) ='TAX'
        AND LINES.HEADER_ID = ADJUST_GST.HEADER_ID(+)
        AND LINES.LINE_ID = ADJUST_GST.LINE_ID(+)
        AND INSTR(ADJUST_GST.TAX_CODE (+),'GST')>0
        AND ADJUST_GST.LIST_LINE_TYPE_CODE(+) ='TAX';
 
      COMMIT;      

   END POPULATEBFR13MONTHORDERLINES; 

  
END GETCLOSEDORDERDATA; 
/ 